﻿using System.Collections.Generic;
using System.Linq;
using DAL.Common;
using DAL.POCO;
using Dapper;
using DapperExtensions;

namespace DAL.Repositories
{
    public class RoundRepository
    {
        public static IEnumerable<Round> GetRounds(int skip, int limit, out int totalItems)
        {
            string sql = @"SELECT * FROM
                              (
                                   SELECT 
                                        ROW_NUMBER() OVER (ORDER BY r.id DESC) as RowNumber, r.id, r.playerX, r.playerY, r.width, r.height, r.time
                                   FROM [Round] r
                              ) as x
                              WHERE RowNumber BETWEEN @startRow AND @endRow";

            string sqlCount = "SELECT COUNT(id) FROM [Round]";

            var rounds = MyConnection.Current.Query<Round>(sql, new { startRow = skip, endRow = skip + limit - 1 });

            totalItems = MyConnection.Current.Query<int>(sqlCount).Single();
            return rounds;
        }

        public static Round GetRound(int roundId)
        {
            string sql = "SELECT id, playerX, playerY, width, height, time FROM [Round] WHERE id = @id";

            var round = MyConnection.Current.Query<Round>(sql, new { id = roundId });
            return round.Single();
        }

        public static void Insert(Round round)
        {
            string sql = "INSERT INTO [Round] (id, playerX, playerY, width, height, time) VALUES (@id, @playerX, @playerY, @width, @height, @time)";

            MyConnection.Current.Execute(sql, new { id = round.id, playerX = round.playerX, playerY = round.playerY, width = round.width, height = round.height, time = round.time });
        }

        public static void Update(Round round)
        {
            MyConnection.Current.Update(round);
        }

        public static void Delete(int roundId)
        {
            string sql = @"DELETE FROM [Field] WHERE roundId = @id;
                           DELETE FROM [Round] WHERE id = @id";

            MyConnection.Current.Execute(sql, new { id = roundId });
        }
    }
}
